Inherited Table Query Planning (fwd) - Mailing list pgsql-performance

Is there a way to make the query planner consider pulling inner appends
outside joins?

Example:
natural_person inherits from person (obviously)

admpostgres3=# explain analyze select u.name, p.name from users u, person p
where p.user_id = u.id and u.name = 's_ohl';
                                                              QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=8.01..3350.14 rows=3 width=36) (actual
time=107.391..343.657 rows=10 loops=1)
   Hash Cond: ("outer".user_id = "inner".id)
   ->  Append  (cost=0.00..2461.34 rows=117434 width=20) (actual
time=0.007..264.910 rows=117434 loops=1)
         ->  Seq Scan on person p  (cost=0.00..575.06 rows=31606 width=20)
(actual time=0.005..38.911 rows=31606 loops=1)
         ->  Seq Scan on natural_person p  (cost=0.00..1886.28 rows=85828
width=19) (actual time=0.003..104.338 rows=85828 loops=1)
   ->  Hash  (cost=8.01..8.01 rows=2 width=24) (actual time=0.096..0.096
rows=0 loops=1)
         ->  Index Scan using users_name_idx on users u  (cost=0.00..8.01
rows=2 width=24) (actual time=0.041..0.081 rows=10 loops=1)
               Index Cond: ((name)::text = 's_ohl'::text)
 Total runtime: 343.786 ms
(9 rows)

admpostgres3=# explain analyze select u.name, p.name from users u, only
person p where p.user_id = u.id and u.name = 's_ohl' union all select
u.name, p.name from users u, only natural_person p where p.user_id = u.id
and u.name = 's_ohl';
 QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..28.19 rows=3 width=28) (actual time=0.197..0.366
rows=10 loops=1)
   ->  Subquery Scan "*SELECT* 1"  (cost=0.00..14.12 rows=1 width=28)
(actual time=0.159..0.159 rows=0 loops=1)
         ->  Nested Loop  (cost=0.00..14.11 rows=1 width=28) (actual
time=0.157..0.157 rows=0 loops=1)
               ->  Index Scan using users_name_idx on users u
(cost=0.00..8.01 rows=2 width=24) (actual time=0.039..0.075 rows=10 loops=1)
                     Index Cond: ((name)::text = 's_ohl'::text)
               ->  Index Scan using person_user_idx on person p
(cost=0.00..3.03 rows=2 width=8) (actual time=0.006..0.006 rows=0 loops=10)
                     Index Cond: (p.user_id = "outer".id)
   ->  Subquery Scan "*SELECT* 2"  (cost=0.00..14.08 rows=2 width=28)
(actual time=0.036..0.193 rows=10 loops=1)
         ->  Nested Loop  (cost=0.00..14.06 rows=2 width=28) (actual
time=0.033..0.171 rows=10 loops=1)
               ->  Index Scan using users_name_idx on users u
(cost=0.00..8.01 rows=2 width=24) (actual time=0.018..0.049 rows=10 loops=1)
                     Index Cond: ((name)::text = 's_ohl'::text)
               ->  Index Scan using natural_person_user_idx on
natural_person p  (cost=0.00..3.01 rows=1 width=8) (actual
time=0.006..0.007 rows=1 loops=10)
                     Index Cond: (p.user_id = "outer".id)
 Total runtime: 0.475 ms
(14 rows)


Mit freundlichem Gruß
Jens Schicke
--
Jens Schicke              j.schicke@asco.de
asco GmbH              http://www.asco.de
Mittelweg 7              Tel 0531/3906-127
38106 Braunschweig          Fax 0531/3906-400